SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROC [dbo].[usp_BaoCaoKinhDoanhTrongThang]
	@Month	char(2),
	@Year	char(4)
AS
BEGIN
	DECLARE @DoanhThu BIGINT
	SELECT 
	
		(SELECT SUM(CAST(gia AS BIGINT))
		FROM KHACH_HANG_BOOKING kh
		WHERE DATEPART (mm, kh.tg_dat) = @Month AND DATEPART (yy, kh.tg_dat) = @Year AND kh.thanh_toan = 'ROI') AS 'DOANH_THU',
	
		(SELECT CAST(COUNT(*) AS BIGINT)
		FROM KHACH_HANG_BOOKING kh, DU_KHACH dk, tour t
		WHERE kh.SO_BOOKING = dk.SO_BOOKING AND kh.MA_TOUR = t.MA_TOUR AND 
		DATEPART (mm, t.NGAY_DI) = @Month AND DATEPART(yy, t.NGAY_DI) = @Year AND kh.THANH_TOAN = 'ROI') AS 'SOLUONG_DK',

		(SELECT COUNT(*)
		FROM KHACH_HANG_BOOKING kh
		WHERE DATEPART (mm, kh.tg_dat) = @Month AND DATEPART (yy, kh.tg_dat) = @Year) AS 'SOLUONG_KHBOOKING',

		(SELECT COUNT(*)
		FROM KHACH_HANG_BOOKING kh
		WHERE DATEPART (mm, kh.tg_dat) = @Month AND DATEPART (yy, kh.tg_dat) = @Year AND KH.THANH_TOAN = 'ROI') AS 'SOLUONG_KHBOOKING_DATHANHTOAN',

		(SELECT COUNT(*)
		FROM KHACH_HANG_BOOKING kh
		WHERE DATEPART (mm, kh.tg_dat) = @Month AND DATEPART (yy, kh.tg_dat) = @Year AND KH.THANH_TOAN = 'CHUA') AS 'SOLUONG_KHBOOKING_CHUATHANHTOAN',
		
		(SELECT COUNT(*)
		FROM TOUR T
		WHERE DATEPART (mm, t.NGAY_DI) = @Month AND DATEPART(yy, t.NGAY_DI) = @Year AND t.TRANG_THAI = 'MO')  AS 'SOLUONG_TOUR'
END

GO
